﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class HoaDonDAO
    {
        public bool ThemHoaDon(HoaDonDTO hd)
        {
            string sql = "INSERT INTO HoaDon(MaHoaDon, MaNV, MaKH, MaPhieuXuat, NgayThanhToan, MaHTTT, TongGiaTri, GhiChu, Deleted) VALUES (";
            sql += "'"+hd.MaHoaDon+"', '"+hd.MaNV+"', '"+hd.MaKH+"', '"+hd.SoPhieuXuat+"', '"+hd.NgayThanhToan+"', "+hd.MaHTTT+", "+hd.TongGiaTri+", N'"+hd.GhiChu+"', 0)";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }
        public List<HoaDonDTO> LayHoaDon()
        {
            string sql = "SELECT * FROM HoaDon WHERE Deleted = 0 ORDER BY SoTT";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                List<HoaDonDTO> listHD = new List<HoaDonDTO>();
                while (read.Read())
                {
                    HoaDonDTO hd = new HoaDonDTO();
                    hd.MaHoaDon = read.GetString(0);
                    hd.MaNV = read.GetString(1);
                    hd.MaKH = read.GetString(2);
                    hd.SoPhieuXuat = read.GetString(3);
                    hd.NgayThanhToan = read.GetDateTime(4);
                    hd.MaHTTT = read.GetInt32(5);
                    hd.TongGiaTri = read.GetInt32(6);
                    hd.GhiChu = read.GetString(7);
                    listHD.Add(hd);
                }
                conn.Close();
                return listHD;
            }
        }
        public HoaDonDTO LayHoaDonTheoMa(string MaHD)
        {
            string sql = "SELECT * FROM HoaDon WHERE MaHoaDon = '"+MaHD+"'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                HoaDonDTO hd = new HoaDonDTO();
                read.Read();
                hd.MaHoaDon = read.GetString(0);
                hd.MaNV = read.GetString(1);
                hd.MaKH = read.GetString(2);
                hd.SoPhieuXuat = read.GetString(3);
                hd.NgayThanhToan = read.GetDateTime(4);
                hd.MaHTTT = read.GetInt32(5);
                hd.TongGiaTri = read.GetInt32(6);
                hd.GhiChu = read.GetString(7);
                conn.Close();
                return hd;
            }
        }
        public bool SuaHoaDon(HoaDonDTO hd)
        {
            string sql = "UPDATE HoaDon SET MaNV = '"+hd.MaNV+"', MaKH = '"+hd.MaKH+"', MaPhieuXuat = '"+hd.SoPhieuXuat+"', ";
            sql += "NgayThanhToan = '"+hd.NgayThanhToan+"', MaHTTT = "+hd.MaHTTT+", TongGiaTri = "+hd.TongGiaTri+", GhiChu = N'"+hd.GhiChu+"' WHERE MaHoaDon = '"+hd.MaHoaDon+"'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }
        public bool XoaHoaDon(string MaHD)
        {
            string sql = "UPDATE HoaDon SET Deleted = 1 WHERE MaHoaDon = '" + MaHD + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }
    }
}
